<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_employees extends MY_Model
{
	protected $_table = "employees";
	protected $_uid = 'emp_id';
	
	function __construct()
    {
        parent::__construct();
		$this->load->model(array('M_users'));
    }
    
	/*
		Grocery CRUD for all employees except "deleted" employees or employees in the "employee_bin"
	*/
	function insert_employee($data) // $data is an array
	{
		//$temp_uname = $this->generate_username();
		//$temp_password = $this->generate_password();
		$input = array(
				'username'=>$data['given_id'],
				'hashed_password'=>$data['given_id'],
				'userType'=>$data['role'],
				'userStatus'=>'active'
				);
		if($this->M_users->add_user($input) == FALSE)
		{
			return FALSE;
		}else
		{
			$data['id'] = $this->get_id();
			$data['last_name'] = strtolower(trim($data['last_name']));
			$data['first_name'] = strtolower(trim($data['first_name']));
			$data['middle_name'] = strtolower(trim($data['middle_name']));
			
			$data['birthdate'] = $this->format_date_from_groceryCRUD($data['birthdate']);
			$data['hire_date'] = $this->format_date_from_groceryCRUD($data['hire_date']);
			$data['termination_date'] = $this->format_date_from_groceryCRUD($data['termination_date']);
			
			$this->db->insert('employees', $data);
			if($this->db->affected_rows() >= 1)
			{
				return TRUE;
			}
		}
	}
	
	function update_employee($data)
	{
		$data['last_name'] = strtolower(trim($data['last_name']));
		$data['first_name'] = strtolower(trim($data['first_name']));
		$data['middle_name'] = strtolower(trim($data['middle_name']));
		
		$data['birthdate'] = $this->format_date_from_groceryCRUD($data['birthdate']);
		$data['hire_date'] = $this->format_date_from_groceryCRUD($data['hire_date']);
		$data['termination_date'] = $this->format_date_from_groceryCRUD($data['termination_date']);
		
		$user_data = array('userType' => $data['role'],
						'updated_at' => NOW,
						'username' => $data['given_id']);
		$user_id = $data['id'];
			
		if($this->M_users->update_user($user_id, $user_data) == FALSE)
		{
			return FALSE;
		}else
		{
			$this->db->where('id', $data['id']);
			$this->db->update('employees', $data);
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}
	}
	
	function check_url($files_to_upload, $field_info)
	{
		if(is_dir($field_info->upload_path))
		{
			return TRUE;
		}else
		{
			return 'The folder where the contract should be saved does not exist. Please try again later or contact your system administrator.';
		}
	}
	
	function given_name_callback($value, $p_key, $user_type)
	{
		$url = site_url(array('employee_settings','change_settings', $p_key));
		$field = "<input id='field-given_id' name='given_id' type='text' value='$value' maxlength='45' style='width:300px'/>&nbsp&nbsp";
		$field .= "<a href='".site_url('employee_settings/change_emp_password/'.__link($p_key))."' class='btn btn-mini btn-primary' id='change_employee_password'>Change Password</a>";
		return $field;

	}
	
		
	/* Callbacks for get_employee_bin() */
	function destroy_employee($key)
	{
		$emp_data = array('userStatus' => 'deleted');

		if($this->db->set($emp_data)->where('emp_id',$key)->update('employees') == FALSE)
		{
			return FALSE;
		}else
		{
			$sql = "SELECT id FROM employees WHERE emp_id = '$key'";
			$query = $this->db->query($sql);
			foreach($query->result() as $row)
			{
				$id = $row->id;
			}
			
			$data = array('userStatus' => 'deleted', 
						'updated_at'=> NOW);
			$this->db->set($data)->where('id',$id)->update('users');
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}
	}

	/* End of callbacks for get_employee_bin() */
	/*=== End of get_employee_bin() ===*/
	
	
	
	/* Custom functions */		
	// this function generates a string with random letters and/or numbers
	function generate_username()
	{
		$alpha_num = 'abcdefghijklmnopqrstuwxyzABCDEFGHIJKLMNOPQRSTUWXYZ0123456789';
		$length = strlen($alpha_num) - 1;
		$username = array();
		for($i = 0; $i < 15; $i++)
		{
			$n = rand(0, $length);
			$pass[] = $alpha_num[$n];
		}
		return implode($pass);
	}
	
	function generate_password()
	{
		$alpha_num = 'abcdefghijklmnopqrstuwxyzABCDEFGHIJKLMNOPQRSTUWXYZ0123456789';
		$length = strlen($alpha_num) - 1;
		for($i = 0; $i < 7; $i++)
		{
			$n = rand(0, $length);
			$pass[] .= $alpha_num[$n];
		}
		return $pass;
	}
	
	// returns ID of newly inserted user.
	function get_id()
	{
		$sql = "SELECT * FROM users ORDER BY id DESC LIMIT 1";
		$query = $this->db->query($sql);
		foreach($query->result() as $row)
		{
			$id = $row->id;
		}
		return $id;
	}
	
	/* checks duplicates of names in employees
		
		@access: public
		@params: string(concatenated last_name, first_name,middle_name), int
		@return: boolean
		
	*/
	function check_duplicates($name, $id)
	{
		if($id == '')
		{
			$sql = "SELECT * FROM employees WHERE CONCAT(last_name,first_name,middle_name) = ?";
			$query = $this->db->query($sql, array($name));
			return $query->num_rows() > 0 ? TRUE : FALSE;
		}else
		{
			$sql = "SELECT * FROM employees WHERE CONCAT(last_name,first_name,middle_name) = ? AND id != ?";
			$query = $this->db->query($sql, array($name, $id));
			return $query->num_rows() > 0 ? TRUE : FALSE;
		}
	}

	function get_all_employees_array($role= false)
	{
		$query = 'SELECT 
					last_name, 
					first_name,
					emp_id
				FROM
					employees';
		$q = $this->db->query($query);
		
		if($q->num_rows() >=1)
		$query = 'SELECT last_name,first_name,emp_id
				  FROM employees ';
		if($role == false)
		{
			$query .=' ORDER BY last_name';
			$q = $this->db->query($query);
		}else{
			$query .=' WHERE role = ?
					  ORDER BY last_name';
			$q = $this->db->query($query,array($role));
		}
		
		if($q->num_rows() >=1)
		{
			$result[''] = '-- Choose teacher --';
			$result['0'] = 'Unassigned (TBA)';
			foreach($q->result() as $q)
			{
				$result[$q->emp_id] = $q->last_name.','.$q->first_name;
			}
			return $result;
		}else{
			return array(''=>'Unable to Fetch Teachers');
		}	
	}

	public function update_adviser_status($id)
	{
		if(is_numeric($id))
		{
			$id = intval($id);
			$this->db->set('is_adviser',1)->where('emp_id',$id)->update('employees');
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}else{
			return FALSE;
		}
	
	}

	function restore_employee($key)
	{
		$emp_data = array('userStatus' => 'active');

		if($this->db->set($emp_data)->where('emp_id',$key)->update('employees') == FALSE)
		{
			return FALSE;
		}else
		{
			$sql = "SELECT id FROM employees WHERE emp_id = ?";
			$query = $this->db->query($sql, array($key));
			foreach($query->result() as $row)
			{
				$id = $row->id;
			}
			
			$data = array('userStatus' => 'active', 
						'updated_at'=> NOW);
			$this->db->set($data)->where('id',$id)->update('users');
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}
	}
	
	function recycle_employee($key)
	{
		$emp_data = array('userStatus' => 'inactive');

		if($this->db->set($emp_data)->where('emp_id',$key)->update('employees') == FALSE)
		{
			return FALSE;
		}else
		{
			$sql = "SELECT id FROM employees WHERE emp_id = ?";
			$query = $this->db->query($sql, array($key));
			foreach($query->result() as $row)
			{
				$id = $row->id;
			}
			
			$data = array('userStatus' => 'inactive', 
						'updated_at'=> NOW);
			$this->db->set($data)->where('id',$id)->update('users');
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}
	}
	
	// Prepares employee data by: strtoupper($last_name), ucfirst($first_name), ucfirst($middle_name) and return array of names.
	function prepare_employee_data($last_name, $first_name, $middle_name)
	{
		$last_name = strtoupper($last_name);
		$first_name = ucfirst($first_name);
		$middle_name = ucfirst($middle_name);
		
		return array('last_name' => $last_name, 'first_name' => $first_name, 'middle_name' => $middle_name);
	}
	
	function format_date_from_groceryCRUD($date)
	{
		$date_array = explode("/", $date);
		return $mysql_date = $date_array[2] . '-' . $date_array[1] . '-' . $date_array[0];
	}
	
	function email_new_member($last_name, $first_name, $middle_name, $temp_username, $temp_password, $email_add)
	{
		$message = "";
		$this->email->from('sample@example.com','HR');
		$this->email->to($email_add);
		$this->email->subject('Welcome!');
		$message = "Greetings\r\n";
		$message .= "Thank you " . $last_name . ", " . $first_name . " " . substr($middle_name, 0, 1) . ". for joining our company.\r\n\r\n";
		$message .= "This is your temporary credentials:\r\n\r\n";
		$message .= "Username: " . $temp_username . "\r\n";
		$message .= "Password: " . $temp_password . "\r\n\r\n";
		$message .= "Please change your password as soon as you log in the system.\r\n";
		$message .= "Thank you.";
		$this->email->message($message);
		$this->email->send();
		echo $this->email->print_debugger();
	}
	
	function get_employee_data($parameter, $data)
	{
		switch($parameter)
		{
			case 'id':
				$sql = "SELECT * FROM employees WHERE id = ?";
				break;
			
			case 'username':
				$sql = "SELECT * FROM employees WHERE given_id = ?";
				break;
			
			default:
				show_error('Unknown $parameter for M_employees/get_employee_data($parameter,$data)<br />Given Parameter: <strong>' . $parameter . '</strong>' , 401);
		}
		$query = $this->db->query($sql, array($data));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function check_uniq_username($name)
	{
		$query = $this->db->select('given_id')->where('given_id',$name)->get('employees');
		return $query->num_rows() >=1 ? TRUE : FALSE;
	}
	
	public function update_given_id($username, $user_id)
	{
		$sql = "UPDATE employees
				SET given_id = ?
				WHERE id = ?";
		$query = $this->db->query($sql, array($username, $user_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function get_columns()
	{
		$sql = "DESCRIBE employees";
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	
	public function get_employee_all($start,$limit,$all = FALSE)
	{


		$start = is_numeric($start) ? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
		$sql = "SELECT concat(last_name,' , ',first_name,' ',middle_name) as fullname, 
					   role,
					   emp_id,
					   e.id as usr_id,
					   gender,
					   u.username
				FROM employees e
				LEFT JOIN users u ON u.id = e.id
				ORDER BY e.last_name,e.role ";

		if($all == FALSE)
		{
			$sql .= " LIMIT {$limit} , {$start}"; 
		}
			
	
				
		$q = $this->db->query($sql);
		

		return $q->num_rows() >=1 ? $q->result() : FALSE;
	}
	
	public function count_employees()
	{
		// $this->db->join('users u','')->get('employees');
		
		$sql = "SELECT count(emp_id) as total
				FROM employees e ";
		$q = $this->db->query($sql);
		

		return $q->num_rows() >=1 ? $q->row()->total : FALSE;
	}
	
	public function _get_employee_data($id)
	{
		$sql = "SELECT *
				FROM employees e
				LEFT JOIN users u ON u.id = e.id
				WHERE e.emp_id = ?";
		$q = $this->db->query($sql,array($id));
		return $q->num_rows() >=1 ? $q->row() : FALSE;
	}
	
	public function update_employee_profile($data,$id)
	{
	
		$data = (object)$data;
		
		$input['first_name'] =$data->fname;
		$input['last_name'] =$data->lname;
		$input['middle_name'] =$data->mname;
		$input['name_ext'] =$data->name_ext;
		// $input[] =$data->role;
		$input['birthdate'] =$data->dob;
		$input['birthplace'] =$data->pob;
		$input['email'] =$data->email;
		$input['gender'] =$data->gender;
		$input['civil_status'] =$data->civilstat;
		$input['citizenship'] =$data->citiz;
		$input['height'] =$data->height;
		$input['weight'] =$data->weight;
		$input['blood_type'] =$data->bloodt;
		$input['religion'] =$data->religion;
		$input['sss_id_no'] =$data->sss_id;
		$input['pagibig_no'] =$data->pagibig;
		$input['philhealth_no'] =$data->philhealth;
		$input['residential_telno'] =$data->res_tel;
		$input['residential_address'] =$data->res_addr;
		$input['residential_zip'] =$data->res_zip;
		$input['permanent_address'] =$data->perm_add;
		$input['permanent_telno'] =$data->perm_tel;
		$input['permanent_zip'] =$data->perm_zip;
		//$input['church_affiliation'] =$data->ch_aff;
		//$input['church_address'] =$data->ch_add;
		//$input['church_telno'] =$data->ch_telno; 
		$input['father_firstname'] =$data->ffname;
		$input['father_middlename'] =$data->fmname;
		$input['father_surname'] =$data->fsname;
		$input['mother_firstname'] =$data->mfname;
		$input['mother_middlename'] =$data->mmname;
		$input['mother_surname'] =$data->msname;
		$input['spouse_firstname'] =$data->spfname;
		$input['spouse_middlename'] =$data->spmname;
		$input['spouse_surname'] =$data->ssname;
		$input['elem_name'] =$data->elemsname;
		$input['elem_course'] =$data->elemcourse;
		$input['elem_year'] =$data->elemyear;
		$input['elem_units'] =$data->elemunits;
		$input['elem_attendance_from'] =$data->elemattfr;
		$input['elem_attendance_to'] =$data->elemattto;
		$input['elem_honors_recieved'] =$data->elemhonor;
		$input['secondary_name'] =$data->secname;
		$input['secondary_course'] =$data->seccourse;
		$input['secondary_year'] =$data->secyear;
		$input['secondary_units'] =$data->secunit;
		$input['secondary_attendance_from'] =$data->secattfrom;
		$input['secondary_attendance_to'] =$data->secattto;
		$input['secondary_honors_recieved'] =$data->sechonors;
		$input['college_name'] =$data->colname;
		$input['college_course'] =$data->colcourse;
		$input['college_year'] =$data->colyear;
		$input['college_units'] =$data->colunits;
		$input['college_attendance_from'] =$data->colattfrom;
		$input['college_attendance_to'] =$data->colattto;
		$input['college_honors_recieved'] =$data->colhonors;
		$input['voc_name'] =$data->vocname;
		$input['voc_course'] =$data->voccourse;
		$input['voc_year'] =$data->vocyear;
		$input['voc_units'] =$data->vocunits;
		$input['voc_attendance_from'] =$data->vocattfrm;
		$input['voc_attendance_to'] =$data->vocattto;
		$input['voc_honors_recieved'] =$data->vochonors;
		$input['grad_name'] =$data->gradname;
		$input['grad_course'] =$data->gradcourse;
		$input['grad_year'] =$data->gradyear;
		$input['grad_units'] =$data->gradunits;
		$input['grad_attendance_from'] =$data->gradattfrm;
		$input['grad_attendance_to'] =$data->gradattto;
		$input['grad_honors_recieved'] =$data->gradhonors;
		
		$this->db->set($input)->where('emp_id',$id)->update('employees');
		
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function new_employee($data)
	{
		$data = (object)$data;

		$input['first_name'] =$data->fname;
		$input['last_name'] =$data->lname;
		$input['middle_name'] =$data->mname;
		$input['name_ext'] =$data->name_ext;
		$input['role'] =$data->role;
		$input['birthdate'] =$data->dob;
		$input['birthplace'] =$data->pob;
		$input['email'] =$data->email;
		$input['gender'] =$data->gender;
		$input['civil_status'] =$data->civilstat;
		$input['citizenship'] =$data->citiz;
		$input['height'] =$data->height;
		$input['weight'] =$data->weight;
		$input['blood_type'] =$data->bloodt;
		$input['religion'] =$data->religion;
		$input['sss_id_no'] =$data->sss_id;
		$input['pagibig_no'] =$data->pagibig;
		$input['philhealth_no'] =$data->philhealth;
		$input['residential_telno'] =$data->res_tel;
		$input['residential_address'] =$data->res_addr;
		$input['residential_zip'] =$data->res_zip;
		$input['permanent_address'] =$data->perm_add;
		$input['permanent_telno'] =$data->perm_tel;
		$input['permanent_zip'] =$data->perm_zip;
		//$input['church_affiliation'] =$data->ch_aff;
		//$input['church_address'] =$data->ch_add;
		//$input['church_telno'] =$data->ch_telno; 
		$input['father_firstname'] =$data->ffname;
		$input['father_middlename'] =$data->fmname;
		$input['father_surname'] =$data->fsname;
		$input['mother_firstname'] =$data->mfname;
		$input['mother_middlename'] =$data->mmname;
		$input['mother_surname'] =$data->msname;
		$input['spouse_firstname'] =$data->spfname;
		$input['spouse_middlename'] =$data->spmname;
		$input['spouse_surname'] =$data->ssname;
		$input['elem_name'] =$data->elemsname;
		$input['elem_course'] =$data->elemcourse;
		$input['elem_year'] =$data->elemyear;
		$input['elem_units'] =$data->elemunits;
		$input['elem_attendance_from'] =$data->elemattfr;
		$input['elem_attendance_to'] =$data->elemattto;
		$input['elem_honors_recieved'] =$data->elemhonor;
		$input['secondary_name'] =$data->secname;
		$input['secondary_course'] =$data->seccourse;
		$input['secondary_year'] =$data->secyear;
		$input['secondary_units'] =$data->secunit;
		$input['secondary_attendance_from'] =$data->secattfrom;
		$input['secondary_attendance_to'] =$data->secattto;
		$input['secondary_honors_recieved'] =$data->sechonors;
		$input['college_name'] =$data->colname;
		$input['college_course'] =$data->colcourse;
		$input['college_year'] =$data->colyear;
		$input['college_units'] =$data->colunits;
		$input['college_attendance_from'] =$data->colattfrom;
		$input['college_attendance_to'] =$data->colattto;
		$input['college_honors_recieved'] =$data->colhonors;
		$input['voc_name'] =$data->vocname;
		$input['voc_course'] =$data->voccourse;
		$input['voc_year'] =$data->vocyear;
		$input['voc_units'] =$data->vocunits;
		$input['voc_attendance_from'] =$data->vocattfrm;
		$input['voc_attendance_to'] =$data->vocattto;
		$input['voc_honors_recieved'] =$data->vochonors;
		$input['grad_name'] =$data->gradname;
		$input['grad_course'] =$data->gradcourse;
		$input['grad_year'] =$data->gradyear;
		$input['grad_units'] =$data->gradunits;
		$input['grad_attendance_from'] =$data->gradattfrm;
		$input['grad_attendance_to'] =$data->gradattto;
		$input['grad_honors_recieved'] =$data->gradhonors;

		$user['username'] = $data->username; 
		$user['hashed_password'] = $this->M_users->hash_password($data->username); 
		$user['userType'] = $data->role;
		$user['userStatus'] = 'active';
		$user['created_at'] = NOW;
	
		$this->db->insert('users',$user);
		$lid = $this->db->insert_id();
		$input['id'] =$lid;
		$this->db->insert('employees',$input);
		
		
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		
	}
	
	public function ajax_search_employees($ajax= FALSE,$x)
	{
		if($ajax == FALSE)
		{
			
		}else{
			$sql = "SELECT 
					   e.emp_id as value,
					   concat_ws('',e.last_name,' , ',e.first_name,' ',e.middle_name) as label,
					   e.emp_id as id,
					   concat_ws('', e.emp_id,' - ',e.last_name,' , ',e.first_name ) as format
					FROM employees e
					WHERE userStatus = 'active'
					AND (e.first_name LIKE ?
						 OR e.middle_name LIKE ?
						 OR e.last_name LIKE ?
						 OR given_id LIKE ?
						 OR concat_ws('',e.first_name,' ',e.middle_name,' ',e.last_name) LIKE ?
						 OR concat_ws('',e.last_name,' ',e.first_name,' ',e.middle_name) LIKE ?
						 OR concat_ws('',e.middle_name,' ',e.last_name,' ',e.first_name) LIKE ?
						)
					ORDER BY e.last_name
					LIMIT 15";
					
			$q = $this->db->query($sql,explode(',',str_repeat('%'.$x.'%,',7)));
			
			return $q->num_rows() >= 1 ? $q->result() : FALSE;
		}
	}
	
	public function check_employee_id($id)
	{
			$sql = "SELECT emp_id
					FROM employees e
					WHERE emp_id = ?
					AND userStatus = 'active'";
			$q = $this->db->query($sql,[$id]);
			return $q->num_rows() >= 1 ? TRUE : FALSE;	
	}

	public function assign_as_dephead($emp_ids)
	{
		$emp_ids = '"'.implode('","',$emp_ids).'"';

		// pp($emp_ids);


		$update1 = 'UPDATE employees set is_dephead = 0 WHERE 1';
		$update2 = 'UPDATE employees set is_dephead = 1 WHERE emp_id IN ('.$emp_ids.')';


		$this->db->query($update1);
		$this->db->query($update2);

		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}

	/**
	 * Get Employees for Dropdown
	 */
	public function get_employees_for_dd()
	{
		$sql = "
			SELECT
			concat_ws('',p.last_name,' , ',p.first_name,' ',p.middle_name) as fullname,
			role
			FROM employees p
			WHERE userStatus = 'active'
			ORDER BY fullname
		";
		$rs = $this->query($sql);

		$sly[''] = "Select Employees";
		if($rs){
			foreach ($rs as $key => $e) {
				$sly[$e->fullname] = $e->fullname. ' ('.ucwords($e->role).')';
			}
		}
		// vd($sly);
		return $sly;
	}
}
?>